package com.lagou.assignment.dao;

import com.lagou.assignment.model.Student;
import com.lagou.assignment.utils.DruidUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class StudentDaoImp implements StudentDao{

    @Override
    public int insert(Student student) throws SQLException {
        //1.创建QueryRunner 手动模式创建
        QueryRunner qr = new QueryRunner();

        //2.编写占位符sql
        String sql = "insert into student values(?,?,?,?,?,?,?)";

        //3.设置占位符的参数
        Object[] param = {null, student.getSid(), student.getSname(), student.getSex(), student.getBirthday(),student.getEmail(), student.getComment()};

        //4.执行update方法
        Connection connection = DruidUtils.getConnection();
        int i = 0;
        if (connection != null) {
            i = qr.update(connection, sql, param);
        }

        //5.释放资源
        DbUtils.closeQuietly(connection);
        return i;
    }


    @Override
    public int update(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "update student set sid = ?, sname = ?, sex = ?, birthday = ?, email = ?, comment = ? where id = ?";
        Object[] param = {student.getSid(), student.getSname(), student.getSex(), student.getBirthday(), student.getEmail(), student.getComment(), student.getId()};
        return qr.update(sql, param);

    }

    @Override
    public int delete(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "delete from student where id = ?";

        return qr.update(sql, id);
    }



    @Override
    public List<Student> select(String sname) throws SQLException {
        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写sql
        String sql = "select * from student where sname = ?";

        //3.执行查询
        return qr.query(sql, new BeanListHandler<>(Student.class), sname);
    }

    @Override
    public Student select(String type, int id) throws SQLException {

        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写sql

        String sql = "select * from student where " + type + " = ?";

        //3.执行查询
        return qr.query(sql, new BeanHandler<>(Student.class), id);
    }

    @Override
    public Student select(int sid, String sname) throws SQLException {
        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写sql

        String sql = "select * from student where sid = ? and sname = ?";

        Object[] param = {sid, sname};

        //3.执行查询
        return qr.query(sql, new BeanHandler<>(Student.class), param);

    }

    @Override
    public List<Student> select(int page, int limit) throws SQLException {

        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写sql
        String sql = "select * from student limit ?, ?";
        Object[] param = {(page - 1)*limit, limit};
        //3.执行查询
        return qr.query(sql, new BeanListHandler<>(Student.class), param);

    }

    @Override
    public int count() throws SQLException {

        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        String sql = "select count(*) from student";

        Long count = (Long) qr.query(sql, new ScalarHandler<>());

        return count.intValue();
    }


}
